package db;

import model.*;

import java.sql.*;
import java.util.ArrayList;


/**
 * DBCase.java
 */
public class DBCase
{
	private  Connection con;
	private DBVehicle dbVehicle = new DBVehicle();
	private DBDebtor dbDebtor = new DBDebtor();
	private DBClient dbClient = new DBClient();
	
	/** Creates a new instance of DBCase */
	public DBCase()
	{
		con = DBConnection.getInstance().getDBcon();
	}
	
	public int getMaxId(String query){
		ResultSet results;
		int id = -1;
		try
		{ 
		   Statement stmt = con.createStatement();
		   results = stmt.executeQuery(query);
		   if( results.next() )
		   {
			   id = results.getInt(1);
		   }
		}
		catch(Exception e)
		{
		    System.out.println("Query exception: Error in reading maxid" + e);
		}
		return id;
	}
	
	//Implements the methods from the interface
	// get all Cases
	public ArrayList<Case> getAllCases(boolean _retriveAssociation)
	{
		return miscWhere("", _retriveAssociation);
	}
	
	//get one Case having the id
	public Case getCaseById(String _id, boolean _retriveAssociation)
	{
		String wClause = " id = '" + _id + "'";
		return singleWhere(wClause, _retriveAssociation);
	}
	
	public Case getCaseByLicensePlate(String _licensePlate, boolean _retriveAssociation)
	{
		String wClause = " licensePlate = '" + _licensePlate + "'";
		return singleWhere(wClause, _retriveAssociation);
	}
	
	//insert a new case
	public int insertCase(Case _case) throws Exception
	{
		int id = getMaxId("Select max(id) from cases");
        id++;
        
		int rc = -1;
		String query="INSERT INTO Cases(licensePlate, startDate, extensionDate, debt, contract, information, historyArea, processed, endDate, id, clientCvr, debtorCpr)"
		+"  VALUES('"+
		_case.getVehicle().getLicensePlate() +"','"+
		_case.getStartDate() +"','"+
		_case.getExtensionDate() +"','"+
		_case.getDebt() +"','"+
		_case.getContract() +"','"+
		_case.getInformation() +"','"+
		_case.getHistoryArea() +"','"+
		_case.getProcessed() +"','"+
		_case.getEndDate() +"','"+
		id +"','"+
		_case.getClient().getCvr() +"','"+
		_case.getDebtor().getCpr() +"')";
		
		System.out.println("insert : " + query);
		try
		{// insert new case +  dependent
			Statement stmt = con.createStatement();
			stmt.setQueryTimeout(5);
			rc = stmt.executeUpdate(query);
			stmt.close();
		}//end try
		catch(SQLException ex)
		{
			System.out.println("Case not created");
			throw new Exception ("Case is not inserted correct");
		}
		return(rc);
	}
	
	public int updateCase(Case _case)
	{
		Case cases  = _case;
		int rc=-1;
		
		String query="UPDATE Cases SET "+
			//"licensePlate ='"+ cases.getVehicle() +"', "+
			//"startDate ='"+ cases.getStartDate() +"', "+
			"extensionDate ='"+ cases.getExtensionDate() +"', "+
			"endDate ='"+ cases.getEndDate() +"', "+
			"debt ='"+ cases.getDebt() +"', "+
			//"contract ='"+ cases.getContract() +"', "+
			"information ='"+ cases.getInformation() +"', "+
			"historyArea ='"+ cases.getHistoryArea() +"', "+
			"processed ='"+ cases.getProcessed() +"' "+
			//"clientCvr ='"+ cases.getClient() +"', "+
			//"debtorCpr ='"+ cases.getDebtor() +"' "+
			" WHERE id = '"+ cases.getId() +"'";
		System.out.println("Update query:" + query);
		try
		{// update case
			Statement stmt = con.createStatement();
			stmt.setQueryTimeout(5);
			rc = stmt.executeUpdate(query);
			
			stmt.close();
		}//end try
		catch(Exception ex)
		{
			System.out.println("Update exception in Cases db: "+ex);
		}
		return(rc);
	}
	
	public boolean deleteCase(String _id)
	{
               //int rc=-1;
		boolean succeeded = false;
	  
	  	String query="DELETE FROM Cases WHERE id = '" +
				_id + "'";
                System.out.println(query);
	  	try{ // delete from employee
	 		Statement stmt = con.createStatement();
	 		stmt.setQueryTimeout(5);
	 	  	stmt.executeUpdate(query);
	 	  	stmt.close();
	 	  	succeeded = true;
  		}//slut try	
   	        catch(Exception ex){
	 	  	System.out.println("Delete exception in Cases db: "+ex);
   	        }
		return(succeeded);
	}
	
	//private methods
	//michWere is used whenever we want to select more than one case
	
	
	private ArrayList<Case> miscWhere(String wClause, boolean retrieveAssociation)
	{
		ResultSet results;
		ArrayList<Case> list = new ArrayList<Case>();	
		
		String query =  buildQuery(wClause);
		
		try
		{// read the case from the database
			Statement stmt = con.createStatement();
			stmt.setQueryTimeout(5);
			results = stmt.executeQuery(query);
		
			while( results.next())
			{
				Case cases = new Case();
				cases = buildCase(results);
				list.add(cases);
			}//end while
			stmt.close();
		}//end try
		catch(Exception e)
		{
			System.out.println("Query exception - select: "+e);
			e.printStackTrace();
		}
		return list;
	}
	
	//Singelwhere is used when we only select one case
	private Case singleWhere(String wClause, boolean retrieveAssociation)
	{
		ResultSet results;
		Case cases = new Case();
		
		String query =  buildQuery(wClause);
		System.out.println(query);
		try
		{// read the Cases from the database
			Statement stmt = con.createStatement();
			stmt.setQueryTimeout(5);
			results = stmt.executeQuery(query);
			
			if( results.next())
			{
				cases = buildCase(results);
				//assocaition is to be build
				stmt.close();
				if(retrieveAssociation)
				{
					String licensePlate = cases.getVehicle().getLicensePlate();
					Vehicle vehicle = dbVehicle.getVehicleByLicensePlate(licensePlate, false);
					cases.setVehicle(vehicle);
					System.out.println("Vehicle is seleceted");
					
					String debtorCpr = cases.getDebtor().getCpr();
					Debtor debtor = dbDebtor.getDebtorCpr(debtorCpr, false);
					cases.setDebtor(debtor);
					System.out.println("Debtor is seleceted");
					
					String clientCvr = cases.getClient().getCvr();
					Client client = dbClient.getClientByCvr(clientCvr, false);
					cases.setClient(client);
					System.out.println("Client is seleceted");
				}
			}
			else
			{//no case was found
				cases = null;
			}
		}//end try
		catch(Exception e)
		{
			System.out.println("Query exception: "+e);
		}
		return cases;
	}
	//method to build the query
	private String buildQuery(String wClause)
	{
		String query="SELECT licensePlate, startDate, extensionDate, debt, contract, information, historyArea, processed, endDate, id, clientCvr, debtorCpr FROM Cases";
		
		if (wClause.length()>0)
			query=query+" WHERE "+ wClause;
		return query;
	}
	//method to build a case object
	public Case buildCase(ResultSet results)
	{
		Case cases = new Case();
		try
		{
			cases.setStartDate(results.getString("startDate"));
			cases.setExtensionDate(results.getString("extensionDate"));
			cases.setDebt(results.getString("debt"));
			cases.setContract(results.getString("contract"));
			cases.setInformation(results.getString("information"));
			cases.setHistoryArea(results.getString("historyArea"));
			cases.setProcessed(results.getString("processed"));
			cases.setEndDate(results.getString("endDate"));
			cases.setId(results.getString("id"));
			cases.setVehicle(dbVehicle.getVehicleByLicensePlate(results.getString("licensePlate"), false));
			cases.setClient(dbClient.getClientByCvr(results.getString("clientCvr"), false));
			cases.setDebtor(dbDebtor.getDebtorCpr(results.getString("debtorCpr"), false));
		}
		catch(Exception e)
		{
			System.out.println("error in building the Case object");
		}
		return cases;
		}
}